﻿//Microsoft SQL Server Compact 3.5 Books Online and Samples 
//http://www.microsoft.com/downloads/details.aspx?FamilyID=1ff0529a-eb1f-4044-b4b7-40b00710f7b7&displaylang=en 
//Learn from The Master, Steve Lasker 
//http://blogs.msdn.com/stevelasker/archive/2008/11/25/demos-presentations-links-screencasts-and-videos-for-sql-server-compact.aspx 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections.ObjectModel;
using BusinessEntityObjects;
using System.Data.SqlServerCe;
using Ocean.OceanFramework.SQLCompactDataAccess;

namespace DataLayer
{

public class ContactDAL 
{ 
    public const string CONNECTION_STRING = "Data Source = WPFDisciples.sdf"; 
    
    #region " Declarations " 
    
    private EntityLoader<Contact> _objEntityLoader = new EntityLoader<Contact>(CONNECTION_STRING); 
    
    #endregion 
    
    #region " Constructors " 
    
    public ContactDAL() 
    { 
    } 
    
    #endregion 
    
    #region " Methods " 
    
    public int Count() 
    { 
        DataAccess da = new DataAccess(CONNECTION_STRING); 
        return (int)da.ExecuteScaler(Queries.StoredProcedures.ContactCount); 
    } 
    
    public static ContactDAL CreateInstance() 
    { 
        return new ContactDAL(); 
    } 
    
    public int Delete(Contact obj) 
    { 
        
        if (!obj.HasBeenValidated) { 
            throw new InvalidOperationException("Contact has not yet been validated for delete."); 
        } 
        
        DataAccess da = new DataAccess(CONNECTION_STRING);
        return da.ExecuteNonQuery(Queries.StoredProcedures.ContactDelete, DataAccessHelper.BuildSQLParameter("@ID", SqlDbType.Int, obj.Id)); 
    } 
    
    public List<ContactSearchResult> FillDropDown() 
    { 
        
        EntityLoader<ContactSearchResult> objFillDropDownEntityLoader = new EntityLoader<ContactSearchResult>(CONNECTION_STRING); 
        return objFillDropDownEntityLoader.LoadList(Queries.StoredProcedures.ContactFillDropDown, CommandBehavior.CloseConnection | CommandBehavior.SingleResult); 
    } 
    
    public List<ContactFeed> GetFeeds() 
    { 
        
        EntityLoader<ContactFeed> objGetFeedsEntityLoader = new EntityLoader<ContactFeed>(CONNECTION_STRING); 
        return objGetFeedsEntityLoader.LoadList(Queries.StoredProcedures.ContactGetAllFeeds, CommandBehavior.CloseConnection | CommandBehavior.SingleResult); 
    } 
    
    public int Insert(Contact obj, ref int intNewId) 
    { 
        
        if (!obj.HasBeenValidated) { 
            throw new InvalidOperationException("Contact has not yet been validated for insert."); 
        } 
        
        using (SqlCeConnection cn = new SqlCeConnection(CONNECTION_STRING)) { 
            cn.Open(); 
            
            DataAccess da = new DataAccess(cn);

            Object datBirthday;

            if (obj.Birthday.HasValue == true)
            {
                datBirthday = obj.Birthday.Value;
            }
            else
            {
                datBirthday = DBNull.Value;
            }

            int intAffectedRows = da.ExecuteNonQuery(Queries.StoredProcedures.ContactInsert, DataAccessHelper.BuildSQLParameter("@FIRSTNAME", SqlDbType.NVarChar, 30, obj.FirstName), DataAccessHelper.BuildSQLParameter("@LASTNAME", SqlDbType.NVarChar, 30, obj.LastName), DataAccessHelper.BuildSQLParameter("@BIRTHDAY", SqlDbType.DateTime, true, datBirthday), DataAccessHelper.BuildSQLParameter("@NUMBEROFCOMPUTERS", SqlDbType.Int, obj.NumberOfComputers), DataAccessHelper.BuildSQLParameter("@BLOGURL", SqlDbType.NVarChar, 100, obj.BlogURL), DataAccessHelper.BuildSQLParameter("@FEEDURL", SqlDbType.NVarChar, 100, obj.FeedURL), DataAccessHelper.BuildSQLParameter("@COMMENT", SqlDbType.NVarChar, 50, obj.Comment)); 
            
            if (intAffectedRows == 1) { 
                
                //C# developers, please forgive Karl for not knowing how to do this!
                //ExecuteScaler returns a Object, but for some reason wants to make it a decimal.
                //
                // Please email Karl the fix.  moleproject@yahoo.com
                //
                object temp = da.ExecuteScaler(Queries.StoredProcedures.SelectLastIdentity);

                decimal temp2 = (decimal)temp;

                intNewId = (int) temp2;

                if (intNewId != 0) { 
                    return 1; 
                } 
                
                else { 
                    return 0; 
                } 
            } 
            
        } 
        
        return 0; 
    } 
    
    public List<ContactSearchResult> SearchByLastName(string strSearchString) 
    { 
        
        EntityLoader<ContactSearchResult> objSearchResultsEntityLoader = new EntityLoader<ContactSearchResult>(CONNECTION_STRING); 
        return objSearchResultsEntityLoader.LoadList(Queries.StoredProcedures.ContactSearchByLastName, CommandBehavior.CloseConnection | CommandBehavior.SingleResult, DataAccessHelper.BuildSQLParameter("@SEARCHSTRING", SqlDbType.NVarChar, 30, strSearchString)); 
    } 
    
    public Contact Select(int intId) 
    { 
        return _objEntityLoader.LoadOne(Queries.StoredProcedures.ContactSelectById, CommandBehavior.CloseConnection | CommandBehavior.SingleResult | CommandBehavior.SingleRow, DataAccessHelper.BuildSQLParameter("@ID", SqlDbType.Int, intId)); 
    } 
    
    public ObservableCollection<Contact> SelectAll() 
    { 
        return _objEntityLoader.LoadObservableColleciton(Queries.StoredProcedures.ContactSelectAll, CommandBehavior.CloseConnection | CommandBehavior.SingleResult); 
    } 
    
    public int Update(Contact obj) 
    { 
        
        if (!obj.HasBeenValidated) { 
            throw new InvalidOperationException("Contact has not yet been validated for update."); 
        } 
        
        DataAccess da = new DataAccess(CONNECTION_STRING);
         
        return da.ExecuteNonQuery(Queries.StoredProcedures.ContactUpdate, DataAccessHelper.BuildSQLParameter("@ID", SqlDbType.Int, obj.Id), DataAccessHelper.BuildSQLParameter("@FIRSTNAME", SqlDbType.NVarChar, 30, obj.FirstName), DataAccessHelper.BuildSQLParameter("@LASTNAME", SqlDbType.NVarChar, 30, obj.LastName), DataAccessHelper.BuildSQLParameter("@BIRTHDAY", 
            SqlDbType.DateTime, true, (obj.Birthday.HasValue ? obj.Birthday : null )), DataAccessHelper.BuildSQLParameter("@NUMBEROFCOMPUTERS", SqlDbType.Int, obj.NumberOfComputers), DataAccessHelper.BuildSQLParameter("@BLOGURL", SqlDbType.NVarChar, 100, obj.BlogURL), DataAccessHelper.BuildSQLParameter("@FEEDURL", SqlDbType.NVarChar, 100, obj.FeedURL), DataAccessHelper.BuildSQLParameter("@COMMENT", SqlDbType.NVarChar, 50, obj.Comment)); 
    } 
}

}

#endregion 
